/*
	exec sp_missed_orders_report '0','0','0','0','0','0','0','0','0',1
*/
ALTER   proc sp_missed_orders_report (

@from_date varchar(20),
@to_date varchar(20),
@c_id varchar(10),	
@pt_id varchar(10),
@region varchar(50),
@rep_office varchar(50),
@end_user varchar(50),
@sales_person varchar(50),
@default_prompt varchar(20),
@order_type int 
)
as
begin
declare @max_change_no int      -- Added By Basha (11/20/2003)
declare @change_date     datetime
	declare @results Table(So_No varchar(50),SalesOrderID int, LineItemNo int,CoolSchdDate datetime,ActSchdDate datetime,Status varchar(20),ProductID int, PartName varchar(50), CustomerID int, CustomerName varchar(65),SODate datetime,ReqDockDate datetime, ReqQty int,QtyShipped int, UnitCost float,Region varchar(50), RepOffice varchar(50),EndUser varchar(65),SalesPerson varchar(50),ChangeDate datetime,EndCustomer varchar(65))
	declare @so_id int, @line_item_id int,@line_item_no int, @qty_shipped int,@act_qty int, @so_no varchar(50), @cust_ship_id int, @cst_id int, @product_id int,@cust_id int
declare @cool_schd_date datetime,@act_date datetime, @so_date datetime,@cool_req_date datetime
declare @unit_price float, @extended_price float
declare @status varchar(20),@so_status varchar(20),@part_no varchar(50), @cust_name varchar(65),@cust_region varchar(50),@cust_rep_office varchar(50),@so_rep_office varchar(50), @so_end_user varchar(65),@so_sales_person varchar(50)	
declare @CSLITQtyshipped int,@CCOstatus varchar(20),@CSLITID int,@EndCustomer varchar(65) 
declare mainCursor cursor for 
	select salesOrder_ID, SO.so_status, SalesOrder_No, SO.Customer_ID, SO.SO_Booking_Date,			 
		CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) 
			THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
			END AS EndUser,
		SO.SO_Sales_Person, SO.Regional_Rep,
		CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
			THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
				THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
				ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
				END) 
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
			END AS EndCustomer
		from sales_order SO where SO.SO_Is_Sample = @order_type
open mainCursor
fetch next from mainCursor into @so_id, @so_status, @so_no,@cust_id,@so_date,@so_end_user,@so_sales_person,@so_rep_office,@EndCustomer
while @@fetch_status = 0
	begin
	if  @so_status = 'APPROVED'
	begin
		declare coolCursor cursor for
		select LineItem_Id, salesorder_ID, cool_line_item_no, cool_status,cool_schedule_date,cool_qty,Product_ID,  cool_requested_date,cool_unit_cost
			from customer_open_order_lineitem 
			where salesOrder_id = @so_id and cool_schedule_date is not null 
		open coolCursor 
		fetch next from coolCursor  into @line_item_id, @so_id,@line_item_no, @status, @cool_schd_date,@act_qty,@product_id,@cool_req_date,@unit_price
		while @@fetch_status = 0
		begin
			select @max_change_no = isnull( max(ChangeNo),0 ) from customer_change_order_lineitem where lineitem_id = @line_item_id
			if ( @max_change_no = 0)
			begin						
				set @change_date = null
			end
			else
			begin
				select @change_date = CCO_Booking_Date from customer_change_order_lineitem where lineitem_id = @line_item_id and ChangeNo = @max_change_no
			end
			select @part_no=Part_Number from product where Product_ID = @product_id
			select @cust_name = C_Name,@cust_region=C_Region from customer where Customer_ID = @cust_id
			if @status = 'APPROVED'
			begin
				-- for the missed orders I had to convert the status of CSLIT from issued to shipped
				declare @temp_status varchar(20)
				set @temp_status = 'APPROVED'
				select @qty_shipped=(select sum(CSLIT_Qty_shipped) from customer_shipping_line_item_table where lineitem_id =@line_item_id and CSLIT_Status='shipped')
				if @qty_shipped is not null
				begin
					if (@act_qty > @qty_shipped) or (@act_qty = @qty_shipped)
					begin
						if (@act_qty > @qty_shipped) 
						begin
							set @temp_status = 'PARTIALLY SHIPPED'
						end
						else if (@act_qty = @qty_shipped) 
						begin
							set @temp_status = 'SHIPPED'
						end													
						declare cslitCursor cursor for
						select distinct CustomerShipping_ID,CSLIT_ID from 
						customer_shipping_line_item_table where 
						lineitem_id =@line_item_id and CSLIT_status = 'shipped'
						open cslitCursor
						fetch next from cslitCursor into @cst_id,@CSLITID	 
						while @@fetch_status = 0
						begin
							select @CSLITQtyshipped = CSLIT_Qty_shipped from customer_shipping_line_item_table where CSLIT_ID = @CSLITID
							select @act_date = cst_ship_date from customer_shipping_table where customerShipping_id = @cst_id
							if @act_date > @cool_schd_date
							begin									
								-- these are shipped but not in time
								insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date,@temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date,@EndCustomer)
							end								 
							else if (@act_qty > @qty_shipped) and (@cool_schd_date < CURRENT_TIMESTAMP)
							begin									
								-- these are partially shipped and the rest of the qty not yet shipped
								insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date,@temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date,@EndCustomer)
							end
							set @CSLITQtyshipped = null									
							set @act_date = null
							fetch next from cslitCursor into @cst_id,@CSLITID	 
						end
						close cslitCursor
						deallocate cslitCursor					
					end
				end
				else if @qty_shipped is null
				begin
					declare @qty_shipped_temp int		
					set @qty_shipped_temp = 0
					if @cool_schd_date < CURRENT_TIMESTAMP
					begin							
						-- these are not shipped and schd date passed by
						insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,null,@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@qty_shipped_temp,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date,@EndCustomer)
					end
				end					
				--This is for the line items which are partially shipped and for the remaining quantity unshipped
				/*if  (@qty_shipped is not null and @cool_schd_date < CURRENT_TIMESTAMP) 
				begin
					set @qty_shipped = 0
					insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,null,@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@qty_shipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date)
				end*/						
			end
			else if @status='SHIPPED'
			begin
				set @temp_status = 'SHIPPED'	
				declare shippedCursor cursor for
				select distinct CustomerShipping_ID,CSLIT_ID from customer_shipping_line_item_table where lineitem_id =@line_item_id and (CSLIT_Status ='SHIPPED' or CSLIT_Status= 'ISSUED')
				open shippedCursor
				fetch next from shippedCursor into @cust_ship_id,@CSLITID
				while @@fetch_status = 0
				begin						
					select @qty_shipped=(select sum(CSLIT_Qty_shipped) from customer_shipping_line_item_table where lineitem_id =@line_item_id and (CSLIT_Status='shipped' or CSLIT_Status='issued'))
					if (@act_qty > @qty_shipped) or (@act_qty = @qty_shipped)
					begin
						if (@act_qty > @qty_shipped)
					begin
						--print 'partially shipped'
						set @temp_status = 'PARTIALLY SHIPPED'
					end				
					else if (@act_qty = @qty_shipped) 
					begin
						--print 'completly shipped'
						set @temp_status = 'SHIPPED'
					end
					select @CSLITQtyshipped = CSLIT_Qty_shipped from customer_shipping_line_item_table where CSLIT_ID = @CSLITID
						select @act_date=cst_ship_date from customer_shipping_table where customerShipping_id = @cust_ship_id 
						if @act_date > @cool_schd_date 
						begin
							-- these are shipped but not in time
							insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date, @temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date,@EndCustomer)
						end
						else if (@act_date <@cool_schd_date) and (@act_qty > @qty_shipped) and (@cool_schd_date < CURRENT_TIMESTAMP)
						begin
							-- these are shipped but not in time
							insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date, @temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date,@EndCustomer)
						end								
						set @qty_shipped = null
						set @act_date = null
						set @cust_region=null
						set @so_rep_office=null
					end						
					fetch next from shippedCursor into @cust_ship_id,@CSLITID
				end
				close shippedCursor
				deallocate shippedCursor									
			end
			--this is for the line items when SO in Approved state and Line item partially shipped and the remaining quantity changed to cancelled and hence the SO status would be in approved state but the line item status would be changed to hold state
			else if @status = 'HOLD'
			begin
				select @qty_shipped=(select sum(CSLIT_Qty_shipped) from customer_shipping_line_item_table where lineitem_id =@line_item_id and CSLIT_Status='shipped')
				if @qty_shipped is not null
				begin
					if (@act_qty > @qty_shipped) or (@act_qty = @qty_shipped)
					begin
						if (@act_qty > @qty_shipped) 
						begin
							set @temp_status = 'PARTIALLY SHIPPED'
							--print 'Partially shipped'
						end
						else if (@act_qty = @qty_shipped) 
						begin
							set @temp_status = 'SHIPPED'
							--print 'Completely shipped'
						end														
						declare cslitCursor cursor for
						select distinct CustomerShipping_ID,CSLIT_ID from 
						customer_shipping_line_item_table where 
						lineitem_id =@line_item_id and CSLIT_status = 'shipped'
						open cslitCursor
						fetch next from cslitCursor into @cst_id,@CSLITID	 
						while @@fetch_status = 0
						begin							
							select @CSLITQtyshipped = CSLIT_Qty_shipped from customer_shipping_line_item_table where CSLIT_ID = @CSLITID
							select @act_date = cst_ship_date from customer_shipping_table where customerShipping_id = @cst_id
							if @act_date > @cool_schd_date
							begin									
								-- these are shipped but not in time
								insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date,@temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date,@EndCustomer)
							end							
							else if (@act_date <@cool_schd_date) and (@act_qty > @qty_shipped) and (@cool_schd_date < CURRENT_TIMESTAMP)
							begin									
								-- these are partially shipped and the rest of the qty not yet shipped
								insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date,@temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date,@EndCustomer)
							end						
						set @CSLITQtyshipped = null								
						set @act_date = null
						fetch next from cslitCursor into @cst_id,@CSLITID	 
						end
						close cslitCursor
						deallocate cslitCursor					
					end
				end
				-- these are not shipped and schd date passed by
				else if @qty_shipped is null
				begin
					set @qty_shipped_temp = 0
						if @cool_schd_date < CURRENT_TIMESTAMP
						begin									
							insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,null,@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@qty_shipped_temp,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date,@EndCustomer)
						end
					end					
				--This is for the line items which are partially shipped and for the remaining quantity unshipped
				/*if (@qty_shipped is not null and @cool_schd_date < CURRENT_TIMESTAMP) 
				begin						
					set @qty_shipped = 0						
					insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,null,@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@qty_shipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date)
				end*/																	
			end				
			set @line_item_id = null 
			set @status = null
			set @cust_region=null
			set @so_rep_office=null
			fetch next from coolCursor into @line_item_id, @so_id,@line_item_no, @status, @cool_schd_date,@act_qty,@product_id,@cool_req_date,@unit_price
		end	
		close coolCursor 
		deallocate coolCursor 
	end
	else if @so_status = 'HOLD' or @so_status = 'PENDING'  
	begin			
		declare holdCursor cursor for
		select LineItem_Id, salesorder_ID, cool_line_item_no, cool_status,cool_schedule_date,cool_qty,Product_ID,cool_requested_date,cool_unit_cost  
			from customer_open_order_lineitem 
			where salesOrder_id = @so_id and cool_schedule_date is not null 
		open holdCursor  				
		fetch next from holdCursor into @line_item_id, @so_id,@line_item_no, @status, @cool_schd_date,@act_qty,@product_id,@cool_req_date,@unit_price
		while @@fetch_status = 0
		begin
			select @max_change_no = isnull( max(ChangeNo),0 ) from customer_change_order_lineitem where lineitem_id = @line_item_id
			if (@max_change_no = 0) 
			begin
				set @change_date = null
			end
			else
			begin
				select @change_date = CCO_Booking_Date from customer_change_order_lineitem where lineitem_id = @line_item_id and ChangeNo = @max_change_no
			end
			select @part_no=Part_Number from product where Product_ID = @product_id
			select @cust_name = C_Name,@cust_region=C_Region from customer where Customer_ID = @cust_id
			-- These are for the sales orders which are in hold and line items in appoved state which means they may be shipped or partially shipped 
			if @status = 'APPROVED'
			begin					
				set @temp_status = 'APPROVED'		
				select @qty_shipped=(select sum(CSLIT_Qty_shipped) from customer_shipping_line_item_table where lineitem_id =@line_item_id and CSLIT_Status='shipped')
				if @qty_shipped is not null
				begin
					if (@act_qty > @qty_shipped) or (@act_qty = @qty_shipped)
					begin
						if (@act_qty > @qty_shipped) 
						begin
							set @temp_status = 'PARTIALLY SHIPPED'
							--print 'Partially shipped'
						end
						else if (@act_qty = @qty_shipped) 
						begin
							set @temp_status = 'SHIPPED'
							--print 'Completely shipped'
						end													
						declare cslitCursor cursor for
						select distinct CustomerShipping_ID,CSLIT_ID from 
						customer_shipping_line_item_table where 
						lineitem_id =@line_item_id and CSLIT_status = 'shipped'
						open cslitCursor
						fetch next from cslitCursor into @cst_id,@CSLITID	 
						while @@fetch_status = 0
						begin							
							select @CSLITQtyshipped = CSLIT_Qty_shipped from customer_shipping_line_item_table where CSLIT_ID = @CSLITID
							select @act_date = cst_ship_date from customer_shipping_table where customerShipping_id = @cst_id
							if @act_date > @cool_schd_date
							begin									
								-- these are shipped but not in time
								insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date,@temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date,@EndCustomer)
							end
							else if (@act_date <@cool_schd_date) and (@act_qty > @qty_shipped) and (@cool_schd_date < CURRENT_TIMESTAMP)
							begin
								-- these are partially shipped and the rest of the qty not yet shipped
								insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date,@temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date,@EndCustomer)
							end						
							set @CSLITQtyshipped = null								
							set @act_date = null
							fetch next from cslitCursor into @cst_id,@CSLITID	 
						end
						close cslitCursor
						deallocate cslitCursor					
					end
				end
				-- these are not shipped and schd date passed by
				else if @qty_shipped is null
				begin
					set @qty_shipped_temp = 0
					if @cool_schd_date < CURRENT_TIMESTAMP
					begin	
						insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,null,@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@qty_shipped_temp,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date,@EndCustomer)
					end
				end				
				--This is for the line items which are partially shipped and for the remaining quantity unshipped
				/*if (@qty_shipped is not null and @cool_schd_date < CURRENT_TIMESTAMP)
				begin
					set @qty_shipped = 0							
					insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,null,@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@qty_shipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date)
				end*/							
			end 
			else if @status = 'HOLD'
			begin					
				-- This is for the line items which are partially shipped twice and then the remaining 
				--quantity is cancelled but not approved .So the status in the COOL table changes to hold 
				set @temp_status = 'APPROVED'						
				select @qty_shipped=(select sum(CSLIT_Qty_shipped) from customer_shipping_line_item_table where lineitem_id =@line_item_id and CSLIT_Status='shipped')
				if @qty_shipped is not null
				begin
					if (@act_qty > @qty_shipped) or (@act_qty = @qty_shipped)
					begin
						if (@act_qty > @qty_shipped) 
						begin
							set @temp_status = 'PARTIALLY SHIPPED'
							--print 'Partially shipped'
						end
						else if (@act_qty = @qty_shipped) 
						begin
							set @temp_status = 'SHIPPED'
							--print 'Completely shipped'
						end													
						declare cslitCursor cursor for
							select distinct CustomerShipping_ID,CSLIT_ID from 
							customer_shipping_line_item_table where 
							lineitem_id =@line_item_id and CSLIT_status = 'shipped'
						open cslitCursor
						fetch next from cslitCursor into @cst_id,@CSLITID 
						while @@fetch_status = 0
						begin
							select @CSLITQtyshipped = CSLIT_Qty_shipped from customer_shipping_line_item_table where CSLIT_ID = @CSLITID
							select @act_date = cst_ship_date from customer_shipping_table where customerShipping_id = @cst_id
							if @act_date > @cool_schd_date
							begin									
								-- these are shipped but not in time
								insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date,@temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date,@EndCustomer)
							end							
							else if (@act_date <@cool_schd_date) and (@act_qty > @qty_shipped) and (@cool_schd_date < CURRENT_TIMESTAMP)
							begin
								-- these are partially shipped and the rest of the qty not yet shipped
								insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date,@temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date,@EndCustomer)
							end						
							set @CSLITQtyshipped = null	
							set @act_date = null
							fetch next from cslitCursor into @cst_id,@CSLITID	 
						end
						close cslitCursor
						deallocate cslitCursor					
					end
				end
				-- these are not shipped and schd date passed by
				else if @qty_shipped is null
				begin
					set @qty_shipped_temp = 0
					if @cool_schd_date < CURRENT_TIMESTAMP
					begin	
						insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,null,@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@qty_shipped_temp,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date,@EndCustomer)
					end
				end				
				--This is for the line items which are partially shipped and for the remaining quantity unshipped
				/*if (@qty_shipped is not null and @cool_schd_date < CURRENT_TIMESTAMP) 
				begin					
					set @qty_shipped = 0							
					insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,null,@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@qty_shipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date)
				end*/								
			end
			else if  @status = 'SHIPPED'
			begin				
				set @temp_status = 'SHIPPED'
				declare shippedCursor cursor for
				select distinct CustomerShipping_ID,CSLIT_ID from customer_shipping_line_item_table where lineitem_id =@line_item_id and (CSLIT_Status ='SHIPPED' or CSLIT_Status= 'ISSUED')
				open shippedCursor
				fetch next from shippedCursor into @cust_ship_id,@CSLITID
				while @@fetch_status = 0
				begin						
					select @qty_shipped=(select sum(CSLIT_Qty_shipped) from customer_shipping_line_item_table where lineitem_id =@line_item_id and (CSLIT_Status='shipped' or CSLIT_Status='issued'))
					if (@act_qty > @qty_shipped) or (@act_qty = @qty_shipped)
					begin
						if (@act_qty > @qty_shipped)
						begin
							--print 'partially shipped'
							set @temp_status = 'PARTIALLY SHIPPED'
						end				
						else if (@act_qty = @qty_shipped) 
						begin
							--print 'completly shipped'
							set @temp_status = 'SHIPPED'
						end
						select @CSLITQtyshipped = CSLIT_Qty_shipped from customer_shipping_line_item_table where CSLIT_ID = @CSLITID
						select @act_date=cst_ship_date from customer_shipping_table where customerShipping_id = @cust_ship_id 
						if @act_date > @cool_schd_date 
						begin								
							-- these are shipped but not in time
							insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date, @temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date,@EndCustomer)
						end	
						else if (@act_date <@cool_schd_date) and (@act_qty > @qty_shipped) and (@cool_schd_date < CURRENT_TIMESTAMP)
						begin
							-- these are shipped but not in time
							insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date, @temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date,@EndCustomer)
						end
						set @qty_shipped = null
						set @act_date = null							
						set @cust_region=null
						set @so_rep_office=null							
					end
					fetch next from shippedCursor into @cust_ship_id,@CSLITID
				end
				close shippedCursor
				deallocate shippedCursor					
			end
			set @line_item_id = null 
			set @status = null -- Added by Basha (11/20/2003)
			set @cust_region=null
			set @so_rep_office=null
			fetch next from holdCursor into @line_item_id, @so_id,@line_item_no, @status, @cool_schd_date,@act_qty,@product_id,@cool_req_date,@unit_price			
		end
		close holdCursor
		deallocate holdCursor
	end			
	set @so_id = null
	set @so_end_user=null
	set @so_sales_person=null
	set @EndCustomer = null
	fetch next from mainCursor into @so_id, @so_status, @so_no,@cust_id,@so_date,@so_end_user,@so_sales_person,@so_rep_office,@EndCustomer
	end
	close mainCursor
	deallocate mainCursor
	end 
select * from @results order by so_no